********************************************************************************************************
*MERGE YEARLY ACS HOUSING 2011-2020
********************************************************************************************************
******2011********
clear all
import delimited using 2011_Housing.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id



rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0004e under5_total
rename dp05_0004pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0017e median_age2
destring median_age, generate(median_age) force
rename dp05_0021pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0032e white_total
rename dp05_0032pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0033e black_total
rename dp05_0033pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0034e amindian_aknative_total
rename dp05_0034pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0039e asian_total
rename dp05_0039pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0052e other_total
rename dp05_0052pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0053e mixed_total
rename dp05_0053pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0066e hispanic_total
rename dp05_0066pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0081e total_housingunits

drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*

gen check = black_total/total_population
sort check
order check, after(total_population)

gen year=2011

save ACS_H_2011.dta, replace



******2012********
clear all
import delimited using 2012_Housing.csv
g z=substr(name, -6,6) 
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id



rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0004e under5_total
rename dp05_0004pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0017e median_age2
destring median_age, generate(median_age) force
rename dp05_0021pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0032e white_total
rename dp05_0032pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0033e black_total
rename dp05_0033pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0034e amindian_aknative_total
rename dp05_0034pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0039e asian_total
rename dp05_0039pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0052e other_total
rename dp05_0052pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0053e mixed_total
rename dp05_0053pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0066e hispanic_total
rename dp05_0066pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0081e total_housingunits

drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*

gen check = black_total/total_population
sort check
order check, after(total_population)

gen year=2012


save ACS_H_2012.dta, replace


******2013********
clear all
import delimited using 2013_Housing.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id


rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0004e under5_total
rename dp05_0004pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0017e median_age2
destring median_age, generate(median_age) force
rename dp05_0021pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0032e white_total
rename dp05_0032pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0033e black_total
rename dp05_0033pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0034e amindian_aknative_total
rename dp05_0034pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0039e asian_total
rename dp05_0039pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0052e other_total
rename dp05_0052pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0053e mixed_total
rename dp05_0053pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0066e hispanic_total
rename dp05_0066pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0081e total_housingunits

drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*

gen check = black_total/total_population
sort check
order check, after(total_population)

gen year=2013


save ACS_H_2013.dta, replace



******2014********
clear all
import delimited using 2014_Housing.csv
g z=substr(name, -6,6) 
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id


rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0004e under5_total
rename dp05_0004pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0017e median_age2
destring median_age, generate(median_age) force
rename dp05_0021pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0032e white_total
rename dp05_0032pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0033e black_total
rename dp05_0033pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0034e amindian_aknative_total
rename dp05_0034pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0039e asian_total
rename dp05_0039pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0052e other_total
rename dp05_0052pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0053e mixed_total
rename dp05_0053pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0066e hispanic_total
rename dp05_0066pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0081e total_housingunits

drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*

gen check = black_total/total_population
sort check
order check, after(total_population)

gen year=2014

save ACS_H_2014.dta, replace


******2015********
clear all
import delimited using 2015_Housing.csv
g z=substr(name, -6,6) 
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id


rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0004e under5_total
rename dp05_0004pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0017e median_age2
destring median_age, generate(median_age) force
rename dp05_0021pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0032e white_total
rename dp05_0032pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0033e black_total
rename dp05_0033pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0034e amindian_aknative_total
rename dp05_0034pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0039e asian_total
rename dp05_0039pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0052e other_total
rename dp05_0052pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0053e mixed_total
rename dp05_0053pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0066e hispanic_total
rename dp05_0066pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0081e total_housingunits

drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*

gen check = black_total/total_population
sort check
order check, after(total_population)

gen year=2015

save ACS_H_2015.dta, replace



******2016********
clear all
import delimited using 2016_Housing.csv
g z=substr(name, -6,6) 
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id


rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0004e under5_total
rename dp05_0004pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0017e median_age2
destring median_age, generate(median_age) force
rename dp05_0021pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0032e white_total
rename dp05_0032pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0033e black_total
rename dp05_0033pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0034e amindian_aknative_total
rename dp05_0034pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0039e asian_total
rename dp05_0039pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0052e other_total
rename dp05_0052pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0053e mixed_total
rename dp05_0053pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0066e hispanic_total
rename dp05_0066pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0081e total_housingunits

drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*

gen check = black_total/total_population
sort check
order check, after(total_population)

gen year=2016

save ACS_H_2016.dta, replace


******2017********
clear all
import delimited using 2017_Housing.csv
g z=substr(name, -6,6) 
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0005e under5_total
rename dp05_0006pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0018e median_age2
destring median_age, generate(median_age) force
rename dp05_0024pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0037e white_total
rename dp05_0037pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0038e black_total
rename dp05_0038pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0039e amindian_aknative_total
rename dp05_0039pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0044e asian_total
rename dp05_0044pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0057e other_total
rename dp05_0057pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0058e mixed_total
rename dp05_0058pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0071e hispanic_total
rename dp05_0071pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0086e total_housingunits

drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*

gen check = black_total/total_population
sort check
order check, after(total_population)

gen year=2017

save ACS_H_2017.dta, replace


******2018********
clear all
import delimited using 2018_Housing.csv
g z=substr(name, -6,6) 
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id


rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0005e under5_total
rename dp05_0006pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0018e median_age2
destring median_age, generate(median_age) force
rename dp05_0024pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0037e white_total
rename dp05_0037pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0038e black_total
rename dp05_0038pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0039e amindian_aknative_total
rename dp05_0039pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0044e asian_total
rename dp05_0044pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0057e other_total
rename dp05_0057pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0058e mixed_total
rename dp05_0058pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0071e hispanic_total
rename dp05_0071pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0086e total_housingunits


drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*
gen year=2018

save ACS_H_2018.dta, replace


******2019********
clear all
import delimited using 2019_Housing.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id


rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0005e under5_total
rename dp05_0006pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0018e median_age2
destring median_age, generate(median_age) force
rename dp05_0024pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0037e white_total
rename dp05_0037pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0038e black_total
rename dp05_0038pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0039e amindian_aknative_total
rename dp05_0039pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0044e asian_total
rename dp05_0044pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0057e other_total
rename dp05_0057pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0058e mixed_total
rename dp05_0058pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0071e hispanic_total
rename dp05_0071pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0086e total_housingunits


drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*

gen year=2019

save ACS_H_2019.dta, replace


******2020********
clear all
import delimited using 2020_Housing.csv
g z=substr(name, -6,6) 
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename dp05_0001e total_population
rename dp05_0002pe male_percent2
destring male_percent2, generate(male_percent) force
rename dp05_0003pe female_percent2
destring female_percent2, generate(female_percent) force
rename dp05_0005e under5_total
rename dp05_0006pe under5_percent2
destring under5_percent2, generate(under5_percent) force
rename dp05_0018e median_age2
destring median_age, generate(median_age) force
rename dp05_0024pe over65_percent2
destring over65_percent2, generate(over65_percent) force
rename dp05_0037e white_total
rename dp05_0037pe white_percent2
destring white_percent2, generate(white_percent) force
rename dp05_0038e black_total
rename dp05_0038pe black_percent2
destring black_percent2, generate(black_percent) force
rename dp05_0039e amindian_aknative_total
rename dp05_0039pe amindian_aknative_percent2
destring amindian_aknative_percent2, generate(amindian_aknative_percent) force
rename dp05_0044e asian_total
rename dp05_0044pe asian_percent2
destring asian_percent2, generate(asian_percent) force
rename dp05_0057e other_total
rename dp05_0057pe other_percent2
destring other_percent2, generate(other_percent) force
rename dp05_0058e mixed_total
rename dp05_0058pe mixed_percent2
destring mixed_percent2, generate(mixed_percent) force
rename dp05_0071e hispanic_total
rename dp05_0071pe hispanic_percent2
destring hispanic_percent2, generate(hispanic_percent) force
rename dp05_0086e total_housingunits


drop male_percent2 female_percent2 under5_percent2 median_age2 over65_percent2 white_percent2 black_percent2 amindian_aknative_percent2 asian_percent2 other_percent2 mixed_percent2 hispanic_percent2 geo_id name dp*

gen year=2020

save ACS_H_2020.dta, replace



clear all

use ACS_H_2011.dta, clear
append using ACS_H_2012.dta
append using ACS_H_2013.dta
append using ACS_H_2014.dta
append using ACS_H_2015.dta
append using ACS_H_2016.dta
append using ACS_H_2017.dta
append using ACS_H_2018.dta
append using ACS_H_2019.dta
append using ACS_H_2020.dta

save ACS_H_2011_2020.dta, replace


********************************************************************************************************
*MERGE YEARLY ACS INCOME 2011-2020
********************************************************************************************************


******2011********
clear all
import delimited using 2011_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2011

save ACS_I_2011.dta, replace


******2012********
clear all
import delimited using 2012_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2012

save ACS_I_2012.dta, replace


******2013********
clear all
import delimited using 2013_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2013

save ACS_I_2013.dta, replace


******2014********
clear all
import delimited using 2014_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2014

save ACS_I_2014.dta, replace



******2015********
clear all
import delimited using 2015_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2015

save ACS_I_2015.dta, replace



******2016********
clear all
import delimited using 2016_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2016

save ACS_I_2016.dta, replace


******2017********
clear all
import delimited using 2017_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2017

save ACS_I_2017.dta, replace



******2018********
clear all
import delimited using 2018_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2018

save ACS_I_2018.dta, replace



******2019********
clear all
import delimited using 2019_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2019

save ACS_I_2019.dta, replace




******2020********
clear all
import delimited using 2020_Income.csv
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s1901_c01_001e total_households
rename s1901_c02_001e total_families
rename s1901_c01_002e hh_lessthan100002
destring hh_lessthan100002, generate(hs_lessthan10000) force
rename s1901_c01_003e hh_10000_149992
destring hh_10000_149992, generate(hh_10000_14999) force
rename s1901_c01_004e hh_15000_249992
destring hh_15000_249992, generate(hh_15000_24999) force
rename s1901_c01_005e hh_25000_349992
destring hh_25000_349992, generate(hh_25000_34999) force
rename s1901_c01_006e hh_35000_499992
destring hh_35000_499992, generate(hh_35000_49999) force
rename s1901_c01_007e hh_50000_749992
destring hh_50000_749992, generate(hh_50000_74999) force
rename s1901_c01_008e hh_75000_999992
destring hh_75000_999992, generate(hh_75000_99999) force
rename s1901_c01_009e hh_100000_1499992
destring hh_100000_1499992, generate(hh_100000_149999) force
rename s1901_c01_010e hh_150000_1999992
destring hh_150000_1999992, generate(hh_150000_199999) force
rename s1901_c01_011e hh_morethan2002
destring hh_morethan2002, generate(hh_morethan200000) force
rename s1901_c01_012e median_income2
destring median_income2, generate(median_income) force

drop hh_lessthan100002 hh_10000_149992 hh_15000_249992 hh_25000_349992 hh_35000_499992 hh_50000_749992 hh_75000_999992 hh_100000_1499992 hh_150000_1999992 hh_morethan2002 median_income2 geo_id name s19*


drop if zip < 90001
drop if zip > 96137

gen year=2020

save ACS_I_2020.dta, replace


clear all

use ACS_I_2011.dta, clear
append using ACS_I_2012.dta
append using ACS_I_2013.dta
append using ACS_I_2014.dta
append using ACS_I_2015.dta
append using ACS_I_2016.dta
append using ACS_I_2017.dta
append using ACS_I_2018.dta
append using ACS_I_2019.dta
append using ACS_I_2020.dta

save ACS_I_2011_2020.dta, replace


********************************************************************************************************
*MERGE YEARLY ACS UNITS 2011-2020
********************************************************************************************************

******2011********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2011 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force




drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8


drop if zip < 90001
drop if zip > 96137

gen year=2011

save "C:\RA\CA Disconnections\My Files\ACS_U_2011.dta", replace


******2012********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2012 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force



drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8



drop if zip < 90001
drop if zip > 96137

gen year=2012

save "C:\RA\CA Disconnections\My Files\ACS_U_2012.dta", replace


******2013********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2013 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force


drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8



drop if zip < 90001
drop if zip > 96137

gen year=2013

save "C:\RA\CA Disconnections\My Files\ACS_U_2013.dta", replace




******2014********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2014 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force



drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8



drop if zip < 90001
drop if zip > 96137

gen year=2014

save "C:\RA\CA Disconnections\My Files\ACS_U_2014.dta", replace


******2015********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2015 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force



drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8



drop if zip < 90001
drop if zip > 96137

gen year=2015

save "C:\RA\CA Disconnections\My Files\ACS_U_2015.dta", replace


******2016********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2016 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force



drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8



drop if zip < 90001
drop if zip > 96137

gen year=2016

save "C:\RA\CA Disconnections\My Files\ACS_U_2016.dta", replace



******2017********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2017 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force


drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8



drop if zip < 90001
drop if zip > 96137

gen year=2017

save "C:\RA\CA Disconnections\My Files\ACS_U_2017.dta", replace



******2018********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2018 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force


drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8



drop if zip < 90001
drop if zip > 96137

gen year=2018

save "C:\RA\CA Disconnections\My Files\ACS_U_2018.dta", replace


******2019********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2019 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force


drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8



drop if zip < 90001
drop if zip > 96137

gen year=2019

save "C:\RA\CA Disconnections\My Files\ACS_U_2019.dta", replace

******2020********
clear all
import delimited "C:\RA\CA Disconnections\My Files\2019 Units.csv"
g z=substr(name, -6,6)
destring z, generate(zip)
sort zip
drop if zip < 90001
drop if zip > 96137
duplicates drop zip, force
gen id = _n
sort id

rename s2504_c01_001e occupied_units
rename s2504_c02_001e owner_occupied
rename s2504_c03_001e renter_occupied
*
rename s2504_c02_002e housing_1d
destring housing_1d, generate(housing_1detached) force
rename s2504_c02_003e housing_1a
destring housing_1a, generate(housing_1attached) force
rename s2504_c02_004e housing_2a
destring housing_2a, generate(housing_2apts) force
rename s2504_c02_005e housing_34a
destring housing_34a, generate(housing_34apts) force
rename s2504_c02_006e housing_59a
destring housing_59a, generate(housing_59apts) force
rename s2504_c02_007e housing_10a
destring housing_10a, generate(housing_10apts) force
rename s2504_c02_008e housing_m
destring housing_m, generate(housing_mobile) force
rename s2504_c02_009e housing_14
destring housing_14, generate(housing_2014) force
rename s2504_c02_010e housing_1013
destring housing_1013, generate(housing_2010_2013) force
rename s2504_c02_011e housing_0009
destring housing_0009, generate(housing_2000_2009) force
rename s2504_c02_012e housing_8099
destring housing_8099, generate(housing_1980_1999) force
rename s2504_c02_013e housing_6079
destring housing_6079, generate(housing_1960_1979) force
rename s2504_c02_014e housing_4059
destring housing_4059, generate(housing_1940_1959) force
rename s2504_c02_015e housing_39
destring housing_39, generate(housing_1939) force
rename s2504_c02_016e housing_r1
destring housing_r1, generate(housing_rooms_1) force
rename s2504_c02_017e housing_r23
destring housing_r23, generate(housing_rooms_23) force
rename s2504_c02_018e housing_r45
destring housing_r45, generate(housing_rooms_45) force
rename s2504_c02_019e housing_r67
destring housing_r67, generate(housing_rooms_67) force
rename s2504_c02_020e housing_r8
destring housing_r8, generate(housing_rooms_8) force



drop geo_id name s25*
drop housing_1d housing_1a housing_2a housing_34a housing_59a housing_10a housing_m housing_14 housing_1013 housing_0009 housing_8099 housing_6079 housing_4059 housing_39 housing_r1 housing_r23 housing_r45 housing_r67 housing_r8



drop if zip < 90001
drop if zip > 96137

gen year=2020

save "C:\RA\CA Disconnections\My Files\ACS_U_2020.dta", replace

clear all

use ACS_U_2011.dta, clear
append using ACS_U_2012.dta
append using ACS_U_2013.dta
append using ACS_U_2014.dta
append using ACS_U_2015.dta
append using ACS_U_2016.dta
append using ACS_U_2017.dta
append using ACS_U_2018.dta
append using ACS_U_2019.dta
append using ACS_U_2020.dta

save ACS_U_2011_2020.dta, replace


********************************************************************************************************
*MERGE ACS FILES WITH DISCONNECTION DATA
********************************************************************************************************

clear all

use ACS_H_2011_2020.dta, clear
sort year zip
merge 1:1 year zip using ACS_I_2011_2020.dta
drop _m
save ACS_H_I_2011_2020.dta, replace

clear

use ACS_H_I_2011_2020.dta, replace
sort year zip
merge 1:1 year zip using ACS_U_2011_2020.dta
drop _m
save ACS_all_2011_2020.dta, replace

import delimited ZIP_CA.csv, clear
rename zip_code zip
save zip_ca.dta, replace

use zip_ca.dta, clear
joinby using ACS_all_2011_2020.dta
replace utility="PGE" if utility=="PG&E"
replace utility="SDGE" if utility=="SDG&E"
replace utility="SCE" if utility=="SoCalEdison"

rename number_of_disconnections disconnections
gen month2=1 if month=="January"
replace month2=2 if month=="February"
replace month2=3 if month=="March"
replace month2=4 if month=="April"
replace month2=5 if month=="May"
replace month2=6 if month=="June"
replace month2=7 if month=="July"
replace month2=8 if month=="August"
replace month2=9 if month=="September"
replace month2=10 if month=="October"
replace month2=11 if month=="November"
replace month2=12 if month=="December"
drop month
rename month2 month
label define monthvalue 1 "January" 2 "February" 3 "March" 4 "April" 5 "May" 6 "June" 7 "July" 8 "August" 9 "September" 10 "October" 11 "November" 12 "December"
label values month monthvalue

save zip_data_acs.dta, replace

import excel "LADWPData.xlsx", sheet("Table109 (Page 109)") firstrow clear

destring Zip_Code, generate(zip) force
label define monthvalue 1 "January" 2 "February" 3 "March" 4 "April" 5 "May" 6 "June" 7 "July" 8 "August" 9 "September" 10 "October" 11 "November" 12 "December"
label values month monthvalue

rename Completed disconnections

save LADWPData.dta, replace

use LADWPData.dta, clear
joinby zip using ACS_all_2011_2020.dta
save LADWData_acs.dta, replace

use LADWData_acs.dta, clear
append using zip_data_acs.dta

save LADW_CA_ACS_Merged.dta, replace

use zip_utilities.dta
sort zip utility
merge 1:m zip utility using LADW_CA_ACS_Merged
replace disconnections=0 if disconnections==.
drop _m
save Utility_Data.dta, replace




********************************************************************************************************
*MERGE DISCONNECTION/ACS DATA WITH ENERGY BURDEN, ENERGY COSTS, AND WEATHER DATA
********************************************************************************************************
clear 


import excel using "Energy Burden LEAD.xlsx", sheet("Sheet1") firstrow clear

rename GeographyID tract_id

save energy_burden.dta, replace

use cageo10.dta, clear
destring tract, generate(tract_id)
merge m:1 tract_id using energy_burden.dta

save tmp1.dta, replace

clear

import excel using "Energy Costs LEAD.xlsx", sheet("Sheet1") firstrow clear

rename GeographyID tract_id

save energy_costs.dta, replace

use tmp1.dta, clear
drop _m
merge m:1 tract_id using energy_costs.dta
drop _m

** Cleanup
drop Name County TribalAreas

** Generate zip code level measures of energy burden and energy costs

** Assume missing tract values for burden and cost are average for state
replace AvgEnergyBurdenincome=2.248825 if AvgEnergyBurdenincome==.
replace AvgAnnualEnergyCost=1942.019 if AvgAnnualEnergyCost==.

** Weighted average, where weights are area of geographic overlap between zip code and tracts
gen foo = AvgEnergyBurdenincome*area_wt
sort zip
egen enburden = sum(foo), by(zip)

gen foo2 = AvgAnnualEnergyCost*area_wt
sort zip
egen encost = sum(foo2), by(zip)

drop foo*

** Collapse to zip code level and cleanup
collapse (first) enburden encost, by(zip)

save costs_burden.dta, replace
clear

erase tmp1.dta
erase energy_burden.dta
erase energy_costs.dta


** Merge ACS, disconnections, and energy burden+costs_burden
use costs_burden
sort zip
merge 1:m zip using Utility_Data.dta
tab _m
drop if _m==1 /** Zip codes without disconnections data **/
drop _m

save disconnections_final, replace
clear


** Weather data
use zip_temp, clear
sort zip year month
merge 1:m zip year month using disconnections_final
drop if _m==1 /** Dates without disconnections data **/
drop _m

save disconnections_final, replace

